14. 读取Excel文件

本章概要

  • 学习材料：带多个工作表、日期与数值字段的 Excel 文件。
  • 本章任务：按“基础读取”和“ExcelFile 多表读取”页指定 sheet、dtype、日期与 converters。
  • 完成后你将得到：工作表清单、字段类型、缺失统计和读取后的前五行。
  • 自我检查：核对必需 sheet/字段和行数；文件、工作表缺失或类型转换失败时先检查原因。
  • 拓展练习：把读取合同拓展应用到另一份中国上市公司披露表。

Excel在金融数据交换中的地位

尽管Python和数据库在金融分析中日益重要，Excel仍然是不可替代的工具：

  • 数据交换：数据源和报告的标准格式
  • 人工输入：交易员和分析师的常用工具
  • 遗留系统：许多老系统仍使用Excel

本节学习如何用 pandas 高效读取Excel文件。

read_excel 函数：基础用法

pd.read_excel() 是 Pandas 读取 Excel 文件的核心函数。

基本语法：

df = pd.read_excel(
    'stores.xlsx',       # 文件路径
    sheet_name='2019',   # 工作表名称
    skiprows=1,          # 跳过前1行
    usecols='B:F'        # 只读取B到F列
)

返回一个 DataFrame 对象，可直接用于后续分析。

read_excel 常用参数详解

参数 说明 示例
sheet_name 工作表名或索引 'Sheet1' 或 0
skiprows 跳过的行数 1
usecols 读取的列范围 'B:F' 或列名列表
dtype 指定列的数据类型 {'价格': float}
converters 列转换函数字典 {'Flagship': fix_missing}
nrows 只读取前N行 100

运行前预测｜平台任务：读取Excel并处理缺失值

  • 输入预测：运行前先写出 df、df2、data1、data2 的业务含义、数据类型或取值范围，并判断哪一个输入最可能改变结果。
  • 结果预测：不展开答案，先预测将得到data2 的结果；同时写出方向、数量级或表格/图形结构。
  • 完成要求：能独立说明本任务从输入到“平台任务：读取Excel并处理缺失值”结果的关键步骤，原样录入平台代码并得到可核对的运行结果。

⭐ 平台任务：读取Excel并处理缺失值

展开完整代码（投影默认折叠） 
# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
# 注：stores.xlsx数据文件本地没有，但平台已经内置
import pandas as pd  # 导入Pandas数据分析库

df = pd.read_excel("stores.xlsx",sheet_name="2019", skiprows=1, usecols="B:F")  # 从Excel文件读取数据存入df
print(df.info())  # 输出数据框基本信息

def fix_missing(x):  # 定义函数fix_missing
  return False if x in ["", "MISSING"] else x  # 返回计算结果

# 从Excel文件读取数据存入df2
df2 = pd.read_excel("stores.xlsx",sheet_name="2019", skiprows=1, usecols="B:F",converters={"Flagship": fix_missing})
print(df2.info())  # 输出数据框基本信息
print(df2)  # 输出数据框数据

with pd.ExcelFile("stores.xlsx") as f:  # 使用上下文管理器
  # 从Excel文件读取数据存入data1
  data1 = pd.read_excel(f, "2019", skiprows=1, usecols="B:F", nrows=2,converters={"Flagship": fix_missing})
  # 从Excel文件读取数据存入data2
  data2 = pd.read_excel(f, "2020", skiprows=1, usecols="B:F", nrows=2,converters={"Flagship": fix_missing})

print(data1)  # 输出数据数据

任务复盘｜平台任务：读取Excel并处理缺失值

运行后核对：核对 df、df2、data1、data2 是否按预测参与运算，实际输出是否与预测一致；若不一致，先检查类型、单位、索引/字段和运算顺序。

拓展练习：把输入表替换为本地中国上市公司数据的同结构子集；指出必须保持的字段、数据类型和质量检查。

代码解读：基础读取

import pandas as pd

df = pd.read_excel(
    'stores.xlsx',
    sheet_name='2019',
    skiprows=1,
    usecols='B:F'
)
print(df.info())
  • 从 stores.xlsx 的 2019 工作表读取数据
  • 跳过第1行（通常是标题或说明行）
  • 只读取 B 到 F 列

代码解读：converters 参数处理脏数据

展开完整代码（投影默认折叠） 
def fix_missing(x):
    return False if x in ['', 'MISSING'] else x

df2 = pd.read_excel(
    'stores.xlsx',
    sheet_name='2019',
    skiprows=1,
    usecols='B:F',
    converters={'Flagship': fix_missing}
)
  • 定义 fix_missing 函数：将空字符串和 'MISSING' 替换为 False
  • converters 参数在读取时就完成数据清洗

代码解读：ExcelFile 类读取多个工作表

展开完整代码（投影默认折叠） 
with pd.ExcelFile('stores.xlsx') as f:
    data1 = pd.read_excel(
        f, '2019', skiprows=1,
        usecols='B:F', nrows=2,
        converters={'Flagship': fix_missing}
    )
    data2 = pd.read_excel(
        f, '2020', skiprows=1,
        usecols='B:F', nrows=2,
        converters={'Flagship': fix_missing}
    )
  • ExcelFile 只打开文件一次，多次读取不同工作表
  • nrows=2 限制只读取前2行（用于快速预览）

ExcelFile 类的优势

使用 ExcelFile 类而非多次调用 read_excel 的三大优势：

  • 只打开文件一次：避免多次 I/O 操作，提升效率
  • 适合读取多个工作表：同一文件中的多年数据、多个子公司报表
  • 自动管理文件句柄：with 语句确保文件使用后自动关闭

本节小结

方法 适用场景
pd.read_excel() 读取单个工作表
converters 参数 读取时清洗脏数据
pd.ExcelFile() 高效读取同一文件的多个工作表

核心要点：

  • read_excel 是读取 Excel 的核心函数
  • converters 可在读取阶段完成数据清洗
  • 多工作表场景优先使用 ExcelFile 类

随堂练习

  • 问题 1｜需要准备哪些数据？：带多个工作表、日期与数值字段的 Excel 文件。
  • 问题 2｜需要完成哪些操作？：按“基础读取”和“ExcelFile 多表读取”页指定 sheet、dtype、日期与 converters。
  • 问题 3｜应得到哪些结果？：工作表清单、字段类型、缺失统计和读取后的前五行。
  • 问题 4｜怎样确认结果可靠？：核对必需 sheet/字段和行数；文件、工作表缺失或类型转换失败时先检查原因。
  • 问题 5｜换一个情境，怎样继续应用？：把读取合同拓展应用到另一份中国上市公司披露表。
  • 作答提示：请依次写清所用数据、分析过程、所得结果、核对方法和拓展思考。课程所需数据见前言中的下载入口；教学平台固定题按页面说明完成。

教师参考解答｜答案与说明 1

  • 逐项答案：下方 read_excel 演示基础读取并检查字段/dtype；Excel 便于人工交换多表，但显示格式不等于真实类型；拓展应用到长三角多表工作簿时用 pd.ExcelFile 一次解析后按 sheet 读取，以 sheet 名、字段、行数和证券代码前导零核对。

  • 所用数据与字段：工作簿字段 code、date、value；date 为日期

  • 参考代码或推导（平台保护块之外）：

教师参考解答｜代码 1

展开代码（代码区可独立滚动） 
from io import BytesIO  # 使用内存文件构造结果一致工作簿
import pandas as pd  # 导入表格库
workbook_buffer=BytesIO()  # 创建工作簿缓冲区
with pd.ExcelWriter(workbook_buffer,engine='openpyxl') as workbook_writer:  # 写入教学最小输入
    pd.DataFrame({'code':['000001'],'date':['2026-01-05'],'value':[10.5]}).to_excel(workbook_writer,index=False)  # 保存字段要求
workbook_buffer.seek(0)  # 将读取位置复位
df=pd.read_excel(workbook_buffer,dtype={'code':'string'},parse_dates=['date'])  # 按约定读取
required={'code','date','value'}  # 定义必需字段
assert required<=set(df.columns) and df.loc[0,'code']=='000001'  # 核对字段与前导零
print(df.shape,df.dtypes.astype(str).to_dict())  # 输出结构

教师参考解答｜答案与说明 2

  • 参考结果：(1,3) 与三列类型；证券代码前导零保留
  • 边界 / 局限：Excel 显示格式不等于实际类型
  • 常见错误：代码前导零丢失；日期未解析